using System;
using System.Collections.Generic;
using System.Text;
using Informatika.Common;
using System.Data.OleDb;
using FinanceStats.Entities;
using System.Data;
using System.IO;
using System.Diagnostics;
using FinanceStats.Common;
using System.Xml;

namespace FinanceStats.Business
{
    public delegate void DataImportedHandler(List<Importitem> list);
    
    public class ImportManager
    {
        # region singleton definition
        static readonly ImportManager instance = new ImportManager();

        public static ImportManager Instance
        {
            get
            {
                return instance;
            }
        }
        static ImportManager()
        {
            instance.excelProcess = new Process();
            instance.excelProcess.EnableRaisingEvents = true;
            instance.excelProcess.StartInfo.CreateNoWindow = true;
            instance.excelProcess.Exited += new EventHandler(instance.p_Exited);
        }
        # endregion

        private Process excelProcess;
        public event DataImportedHandler DataImported;

        public void ImportFromXLS(string filename)
        {
            List<Importitem> res = new List<Importitem>();
            OleDbConnection oledbConn = null;
            OleDbCommand cmdSelect = null;
            OleDbDataReader myRdr = null;
            try
            {

                oledbConn = new OleDbConnection();
                cmdSelect = new OleDbCommand();

                String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
                + "Data Source=" + filename + ";"
                + "Extended Properties=Excel 8.0;";

                oledbConn.ConnectionString = sConnectionString;

                oledbConn.Open();

                DataTable schemaTable = oledbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                string SheetName = schemaTable.Rows[0].ItemArray[2].ToString();

                cmdSelect = new OleDbCommand("SELECT * FROM [" + SheetName + "]", oledbConn);

                myRdr = cmdSelect.ExecuteReader();
                if (myRdr != null)
                {
                    while (myRdr.Read())
                    {
                        DateTime date = myRdr.IsDBNull(0) ? DateTime.Now.Date : myRdr.GetDateTime(0);
                        string itemName = myRdr.IsDBNull(1) ? String.Empty : myRdr.GetString(1);
                        double amount = myRdr.IsDBNull(2) ? 0 : myRdr.GetDouble(2);
                        Importitem item = new Importitem();
                        item.Date = date;
                        item.Itemname = itemName;
                        item.Amount = (decimal)amount;
                        if (amount != 0)
                        {
                            res.Add(item);
                        }
                    }
                    myRdr.Close();
                }
                oledbConn.Close();
            }
            catch (Exception ex)
            {
                LogManager.LogException("Error parsing xsl document", ex);
                if (myRdr != null)
                {
                    myRdr.Close();
                }
                if (oledbConn != null && oledbConn.State != ConnectionState.Closed)
                {
                    oledbConn.Close();
                }
                res.Clear();
            }
            if (this.DataImported != null)
            {
                DataImported(res);
            }
            // return res;
        }

        private void ImportFromXML(string fileName)
        {
            List<Importitem> res = new List<Importitem>();
            
            using (XmlTextReader xtReader = new XmlTextReader(fileName))
            {
                while (xtReader.Read())
                {
                    xtReader.MoveToElement();
                    if (xtReader.LocalName == "Row")
                    {
                        DateTime date = DateTime.Today;
                        string name = "";
                        decimal amount = 0;
                        while (xtReader.ReadToFollowing("Cell") && xtReader.ReadToFollowing("Data"))
                        {
                            if (xtReader.HasAttributes && xtReader.GetAttribute(0) == "DateTime")
                            {
                                xtReader.Read();
                                xtReader.MoveToElement();
                                date = xtReader.ReadContentAsDateTime();
                            }
                            if (xtReader.HasAttributes && xtReader.GetAttribute(0) == "String")
                            {
                                xtReader.Read();
                                xtReader.MoveToElement();
                                name = xtReader.ReadContentAsString();
                            }
                            if (xtReader.HasAttributes && xtReader.GetAttribute(0) == "Number")
                            {
                                xtReader.Read();
                                xtReader.MoveToElement();
                                amount = xtReader.ReadContentAsDecimal();
                                Importitem item = new Importitem();
                                item.Date = date;
                                item.Itemname = name;
                                item.Amount = (decimal)amount;
                                if (amount != 0)
                                {
                                    res.Add(item);
                                }
                            }
                        }
                    }
                }
            }
            if (this.DataImported != null)
            {
                DataImported(res);
            }
        }

        public List<UserOutcome> MatchData(ref List<Importitem> inputList, int userID)
        {
            List<UserOutcome> output = new List<UserOutcome>();
            List<Importitem> unrekognized = new List<Importitem>();
            Dictionary<string, int> names = DataManager.Instance.LoadItemNames();
            if (names != null)
            {
                for (int i = 0; i < inputList.Count; i++)
                {
                    Importitem ii = inputList[i];
                    string key = ii.Itemname.ToLower();
                    if (names.ContainsKey(key))
                    {
                        UserOutcome uo = new UserOutcome();
                        uo.UserID = userID;
                        uo.Date = (DateTime)ii.Date;
                        uo.Amount = (decimal)ii.Amount;
                        uo.ItemID = names[key];
                        uo.ItemName = ii.Itemname;
                        output.Add(uo);
                    }
                    else
                    {
                        unrekognized.Add(ii);
                    }
                }
                inputList = unrekognized;
            }
            return output;
        }

        public void GenerateImportTemplate()
        {
            #region ExcelTemplates
            string excelTemplate = @"
<?xml version=""1.0""?>
<?mso-application progid=""Excel.Sheet""?>
<Workbook xmlns=""urn:schemas-microsoft-com:office:spreadsheet""
 xmlns:o=""urn:schemas-microsoft-com:office:office""
 xmlns:x=""urn:schemas-microsoft-com:office:excel""
 xmlns:ss=""urn:schemas-microsoft-com:office:spreadsheet""
 xmlns:html=""http://www.w3.org/TR/REC-html40"">
 <DocumentProperties xmlns=""urn:schemas-microsoft-com:office:office"">
  <Author>Voja</Author>
  <LastAuthor>Voja</LastAuthor>
  <Created>{0}</Created>
  <LastSaved>{0}</LastSaved>
  <Company>PotesXP</Company>
  <Version>11.5606</Version>
 </DocumentProperties>
 <ExcelWorkbook xmlns=""urn:schemas-microsoft-com:office:excel"">
  <WindowHeight>11250</WindowHeight>
  <WindowWidth>18075</WindowWidth>
  <WindowTopX>240</WindowTopX>
  <WindowTopY>30</WindowTopY>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID=""Default"" ss:Name=""Normal"">
   <Alignment ss:Vertical=""Bottom""/>
   <Borders/>
   <Font/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
  <Style ss:ID=""s23"">
   <NumberFormat ss:Format=""[ENG][$-409]d\-mmm\-yy;@""/>
  </Style>
  <Style ss:ID=""s24"">
   <NumberFormat ss:Format=""Fixed""/>
  </Style>
  <Style ss:ID=""s25"">
   <Font x:Family=""Swiss"" ss:Bold=""1""/>
   <NumberFormat ss:Format=""[ENG][$-409]d\-mmm\-yy;@""/>
  </Style>
  <Style ss:ID=""s26"">
   <Font x:Family=""Swiss"" ss:Bold=""1""/>
  </Style>
  <Style ss:ID=""s27"">
   <Font x:Family=""Swiss"" ss:Bold=""1""/>
   <NumberFormat ss:Format=""Fixed""/>
  </Style>
 </Styles>
 <Worksheet ss:Name=""Sheet1"">
  <Table ss:ExpandedColumnCount=""3"" ss:ExpandedRowCount=""{3}"" x:FullColumns=""1""
   x:FullRows=""1"">
   <Column ss:StyleID=""s23"" ss:AutoFitWidth=""0"" ss:Width=""99.75""/>
   <Column ss:AutoFitWidth=""0"" ss:Width=""124.5""/>
   <Column ss:StyleID=""s24"" ss:AutoFitWidth=""0"" ss:Width=""126""/>
   <Row>
    <Cell ss:StyleID=""s25""><Data ss:Type=""String"">Date</Data></Cell>
    <Cell ss:StyleID=""s26""><Data ss:Type=""String"">Outcome name</Data></Cell>
    <Cell ss:StyleID=""s27""><Data ss:Type=""String"">Amount</Data></Cell>
   </Row>
{1}
  </Table>
  <WorksheetOptions xmlns=""urn:schemas-microsoft-com:office:excel"">
   <Print>
    <ValidPrinterInfo/>
    <HorizontalResolution>300</HorizontalResolution>
    <VerticalResolution>300</VerticalResolution>
   </Print>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>{2}</ActiveRow>
     <ActiveCol>1</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>
";

            string rowTemplate = @"
   <Row>
    <Cell><Data ss:Type=""DateTime"">{0}</Data></Cell>
    <Cell><Data ss:Type=""String"">{1}</Data></Cell>
   </Row>
";

            #endregion

            StringBuilder rows = new StringBuilder();
            Dictionary<string, int> itemNames = DataManager.Instance.LoadItemNames();
            int i = 0;
            foreach (KeyValuePair<string, int> kwp in itemNames)
            {
                rows.AppendFormat(rowTemplate, DateTime.Now.ToString("yyyy-MM-dd"), kwp.Key);
                i++;
            }

            bool noError = true;
            String template = String.Format(excelTemplate, DateTime.Now, rows.ToString(), i, i+1);
            FileStream fs = null;
            StreamWriter sw = null;
            try
            {
                fs = File.Create("c:\\temp.xls");
                sw = new StreamWriter(fs);
                sw.Write(template);
                sw.Close();
                fs.Close();
            }
            catch (Exception ex)
            {
                LogManager.LogException("error opening file", ex);
                if (sw != null) sw.Close();
                if (fs != null) fs.Close();
            }
            if (noError)
            {
                excelProcess.StartInfo.FileName = AppSettingsHelper.GetWebConfigValue(Constants.ExcelPath);
                excelProcess.StartInfo.Arguments = new DirectoryInfo("c:\\temp.xls").FullName;
                excelProcess.Start();
                excelProcess.WaitForExit();
            }

        }

        void p_Exited(object sender, EventArgs e)
        {
            ImportFromXML(new DirectoryInfo("c:\\temp.xls").FullName);
        }
    }
}
